package com.limao.Admin.DBProvider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.limao.Admin.DataClass.AdminCityClass;
import com.limao.DbConnection.DbConnectionManager;
import com.limao.Tool.CacheClass;


public class AdminCityProvider {
	
	private static class AdminCityProviderContainer {
		private static AdminCityProvider instance = new AdminCityProvider();
	}

	public static AdminCityProvider getInstance() {
		return AdminCityProviderContainer.instance;
	}
	
	// 初始化
	private AdminCityProvider() {}
	
	public  List<AdminCityClass> queryALLCity()
	{
		List<AdminCityClass> list = new ArrayList<AdminCityClass>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select * from cd_sys_city where parentid=0");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				AdminCityClass obj=new AdminCityClass();
				obj.id = rs.getInt("id") + "";
				obj.cityname = rs.getString("cityname") + "";
				
				int id=CacheClass.getIntValue(obj.id);
				List<AdminCityClass> list2 = new ArrayList<AdminCityClass>();
				Connection con2 = DbConnectionManager.getConnection();
				PreparedStatement pstmt2 = null;
				ResultSet rs2 = null;
				pstmt2 = con2.prepareStatement("select * from cd_sys_city where parentid=? ");
				pstmt2.setInt(1, id);
				rs2 = pstmt2.executeQuery();
				
				while (rs2.next()) {
					AdminCityClass obj2=new AdminCityClass();
					obj2.id = rs2.getInt("id") + "";
					obj2.cityname = rs2.getString("cityname") + "";
					list2.add(obj2);
				}
				DbConnectionManager.closeConnection(rs2, pstmt2, con2);
				obj.list=list2;
				list.add(obj);
			}
		} catch (Exception e) {
			list=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		
		return list;
	}
	
	public  List<AdminCityClass> queryALLCity_Sheng()
	{
		List<AdminCityClass> list = new ArrayList<AdminCityClass>();

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select * from cd_sys_city where parentid=0 order by sort asc");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				AdminCityClass obj=new AdminCityClass();
				obj.id = rs.getInt("id") + "";
				obj.cityname = rs.getString("cityname") + "";
				obj.parentid = rs.getInt("parentid")+"";
				list.add(obj);
			}
		} catch (Exception e) {
			list=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return list;		
	}
	
	
	public  List<AdminCityClass> queryALLCity_BySheng(int shengid)
	{
		List<AdminCityClass> list = new ArrayList<AdminCityClass>();

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select * from cd_sys_city where parentid=? order by sort asc");
			pstmt.setInt(1, shengid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				AdminCityClass obj=new AdminCityClass();
				obj.id = rs.getInt("id") + "";
				obj.cityname = rs.getString("cityname") + "";
				obj.parentid = rs.getInt("parentid")+"";
				list.add(obj);
			}
		} catch (Exception e) {
			list.clear();
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return list;		
	}
	
	public  AdminCityClass queryCity_Byid(int id)
	{
		AdminCityClass obj= null;

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select * from cd_sys_city where id=? ");
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				obj=new AdminCityClass();
				obj.id = rs.getInt("id") + "";
				obj.cityname = rs.getString("cityname") + "";
				obj.parentid = rs.getInt("parentid")+"";
			}
		} catch (Exception e) {
			obj=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return obj;		
	}
	

	
	
}
